from ast import Raise
from flask import Flask, jsonify, request, make_response
import pymysql
from datetime import datetime, timedelta
from db_config import DB_CONFIG
from flask import send_from_directory, send_file
import json
import decimal
import random



app = Flask(__name__)

# 基础路径
BASE_PATH = '/api/v1'

@app.route(f'{BASE_PATH}/dates', methods=['GET'])
def get_dates():
    """获取所有可用的交易日期"""
    try:
        logging.debug('Attempting to connect to database')
        conn = pymysql.connect(**DB_CONFIG)
        logging.debug('Database connection established')
        cursor = conn.cursor(pymysql.cursors.DictCursor)
        logging.debug('Executing query: SELECT trading_date FROM time_object ORDER BY trading_date DESC')
        query = "SELECT trading_date FROM time_object ORDER BY trading_date DESC"
        cursor.execute(query)
        logging.debug('Query execution completed')
        results = cursor.fetchall()
        logging.debug(f'Query results: {results}')
        dates = [row['trading_date'] for row in results]
        cursor.close()
        conn.close()
        logging.debug('Database connection closed')
        response = jsonify({
            'code': 200,
            'message': 'success',
            'data': {
                'dates': dates
            }
        })
        response.headers['X-Content-Type-Options'] = 'nosniff'
        response.headers['Cache-Control'] = 'no-store, no-cache, must-revalidate'
        return response
    except Exception as e:
        logging.error('Error in get_dates: %s', str(e))
        response = jsonify({
            'code': 500,
            'message': '服务器内部错误',
            'error_detail': str(e)
        })
        response.headers['X-Content-Type-Options'] = 'nosniff'
        response.headers['Cache-Control'] = 'no-store, no-cache, must-revalidate'
        return response, 500

@app.route(f'{BASE_PATH}/hit-rate', methods=['GET'])
def get_hit_rate():
    """根据指定日期获取命中率"""
    date = request.args.get('date')
    if not date:
        response = jsonify({
            'code': 400,
            'message': '请求参数错误，缺少日期参数',
        })
        response.headers['X-Content-Type-Options'] = 'nosniff'
        response.headers['Cache-Control'] = 'no-store, no-cache, must-revalidate'
        return response, 400
    
    try:
        # 验证日期格式
        datetime.strptime(date, '%Y-%m-%d')
    except ValueError:
        response = jsonify({
            'code': 400,
            'message': '请求参数错误，日期格式应为 YYYY-MM-DD',
        })
        response.headers['X-Content-Type-Options'] = 'nosniff'
        response.headers['Cache-Control'] = 'no-store, no-cache, must-revalidate'
        return response, 400

    try:
        conn = pymysql.connect(**DB_CONFIG)
        cursor = conn.cursor(pymysql.cursors.DictCursor)
        query = "SELECT hit_rate FROM time_object WHERE trading_date = %s ORDER BY trading_date"
        cursor.execute(query, (date,))
        result = cursor.fetchone()
        cursor.close()
        conn.close()
        
        if result:
            hit_rate = result['hit_rate']
            response = jsonify({
                'code': 200,
                'message': 'success',
                'data': {
                    'hit_rate': hit_rate
                }
            })
        else:
            response = jsonify({
                'code': 404,
                'message': '未找到对应日期的命中率',
            })
        
        response.headers['X-Content-Type-Options'] = 'nosniff'
        response.headers['Cache-Control'] = 'no-store, no-cache, must-revalidate'
        return response
    except pymysql.Error as e:
        logging.error('Database error in get_hit_rate: %s', str(e))
        response = jsonify({
            'code': 500,
            'message': '服务器内部错误',
            'error_detail': str(e)
        })
        response.headers['X-Content-Type-Options'] = 'nosniff'
        response.headers['Cache-Control'] = 'no-store, no-cache, must-revalidate'
        return response, 500
    except Exception as e:
        logging.error('General error in get_hit_rate: %s', str(e))
        response = jsonify({
            'code': 500,
            'message': '服务器内部错误',
            'error_detail': str(e)
        })
        response.headers['X-Content-Type-Options'] = 'nosniff'
        response.headers['Cache-Control'] = 'no-store, no-cache, must-revalidate'
        return response, 500

@app.route(f'{BASE_PATH}/plate-data', methods=['GET'])
def get_plate_data():
    """根据指定日期获取所有分类的板块数据"""
    date = request.args.get('date')
    if not date:
        response = jsonify({
            'code': 400,
            'message': '请求参数错误，缺少日期参数',
        })
        response.headers['X-Content-Type-Options'] = 'nosniff'
        response.headers['Cache-Control'] = 'no-store, no-cache, must-revalidate'
        return response, 400
    
    try:
        # 验证日期格式
        datetime.strptime(date, '%Y-%m-%d')
    except ValueError:
        response = jsonify({
            'code': 400,
            'message': '请求参数错误，日期格式应为 YYYY-MM-DD',
        })
        response.headers['X-Content-Type-Options'] = 'nosniff'
        response.headers['Cache-Control'] = 'no-store, no-cache, must-revalidate'
        return response, 400

    try:
        logging.debug('Attempting to connect to database for plate data')
        conn = pymysql.connect(**DB_CONFIG)
        logging.debug('Database connection established for plate data')
        cursor = conn.cursor(pymysql.cursors.DictCursor)
        
        # 淘汰板块 1
        logging.debug('Executing query for eliminated plates 1')
        query_elim_1 = """SELECT name, daily_gain
                          FROM a_share_trading
                          WHERE trade_date = %s AND is_low_performance_eliminate = '是'
                          ORDER BY CAST(REPLACE(daily_gain, '%%', '') AS DECIMAL(5,2)) ASC
                          LIMIT 20"""
        cursor.execute(query_elim_1, (date,))
        logging.debug('Query execution for eliminated plates 1 completed')
        eliminated_plates_1 = cursor.fetchall()

        # 保留板块 1
        logging.debug('Executing query for reserved plates 1')
        query_res_1 = """SELECT name, daily_gain
                        FROM a_share_trading
                        WHERE trade_date = %s AND is_reserved_in_pool = '是'
                        ORDER BY CAST(REPLACE(daily_gain, '%%', '') AS DECIMAL(5,2)) ASC
                        LIMIT 20"""
        cursor.execute(query_res_1, (date,))
        logging.debug('Query execution for reserved plates 1 completed')
        reserved_plates_1 = cursor.fetchall()

        # 淘汰板块 2
        logging.debug('Executing query for eliminated plates 2')
        query_elim_2 = """SELECT a.name,
                            a.daily_gain,
                            b.main_net_inflow
                        FROM a_share_trading a, a_share_fund_flow b
                        WHERE a.trade_date = %s
                            AND b.flow_date = %s
                            AND a.is_low_performance_eliminate = '是'
                            AND a.`name` = b.`name`
                        ORDER BY CAST(REPLACE(a.daily_gain, '%%', '') AS DECIMAL(5, 2)) DESC
                        LIMIT 20"""
        cursor.execute(query_elim_2, (date, date))
        logging.debug('Query execution for eliminated plates 2 completed')
        eliminated_plates_2 = cursor.fetchall()

        # 保留板块 2
        logging.debug('Executing query for reserved plates 2')
        query_res_2 = """SELECT name, daily_gain
                        FROM a_share_trading
                        WHERE trade_date = %s AND is_reserved_in_pool = '是'
                        ORDER BY CAST(REPLACE(daily_gain, '%%', '') AS DECIMAL(5,2)) DESC
                        LIMIT 20"""
        cursor.execute(query_res_2, (date,))
        logging.debug('Query execution for reserved plates 2 completed')
        reserved_plates_2 = cursor.fetchall()

        # 所有保留板块
        logging.debug('Executing query for all reserved plates')
        #板块名称	综合得分		资金得	事件得分	市盈率	估值得分
        query_all_res = """SELECT name, 
                            total_score,
                            fund_score_base,
                            event_score_base,
                            pe_ratio,
                            valuation_score_base
                          FROM a_share_trading
                          WHERE trade_date = %s AND (is_reserved_in_pool = '是' or is_reserved_in_pool is null)
                          ORDER BY total_score DESC"""
        cursor.execute(query_all_res, (date,))
        logging.debug('Query execution for all reserved plates completed')
        all_reserved_plates = cursor.fetchall()
        all_reserved_plates = get_hedge_plates_info(cursor, date, all_reserved_plates)

        # Top25 推荐板块
        logging.debug('Executing query for top recommended plates')
        query_top_rec = """SELECT name, 
                            daily_gain,
                            total_score,
                            fund_score_base,
                            event_score_base,
                            pe_ratio,
                            valuation_score_base
                          FROM a_share_trading
                          WHERE trade_date = %s AND is_recommended = '是'
                          ORDER BY total_score DESC
                          LIMIT 25"""
        cursor.execute(query_top_rec, (date,))
        logging.debug('Query execution for top recommended plates completed')
        top_recommended_plates = cursor.fetchall()
        top_recommended_plates = get_hedge_plates_info(cursor, date, top_recommended_plates)

        # 昨日推荐命中板块
        try:
            # 从 time_object 表获取前一天的交易日期
            get_prev_date_sql = "SELECT MAX(trading_date) as prev_date FROM time_object WHERE trading_date < %s"
            cursor.execute(get_prev_date_sql, (date,))
            prev_date_result = cursor.fetchone()
            if not prev_date_result['prev_date']:
                logging.debug('未找到前一天的交易数据，无法获取昨日推荐命中板块')
                raise ValueError('未找到前一天的交易数据，无法获取昨日推荐命中板块')
                
            else:
                prev_date = prev_date_result['prev_date']
                logging.debug('Executing query for yesterday hit plates')
                query_hit = """SELECT name, total_score, hit_status, 
                                daily_gain, leading_stock
                              FROM a_share_trading
                              WHERE trade_date = %s
                              ORDER BY daily_gain DESC"""
                cursor.execute(query_hit, (prev_date,))
                logging.debug('Query execution for yesterday hit plates completed')
                all_plates_yesterday = cursor.fetchall()
                yesterday_hit_plates = [item for item in all_plates_yesterday if item['hit_status'] != None and
                        item['total_score'] != None]
                name_to_rank_yesterday = {plate['name']: idx + 1 for idx, plate in enumerate(all_plates_yesterday)}
                # 这里需要给yesterday_hit_plates中的数据添加一个字段，字段名为rank_in_nextday，
                # 把今日最新的板块的涨幅数据拿出来，从高到底排列，然后在yesterday_hit_plates相应
                # 的数据中找到对应的板块，把该板块的排名赋值给rank_in_nextday字段
                # 对今日涨幅板块按涨幅从高到低排序，并生成排名映射
                logging.debug('Executing query for today top gain plates')
                new_day_query = """SELECT name, daily_gain, leading_stock
                                FROM a_share_trading
                                WHERE trade_date = %s
                                ORDER BY CAST(REPLACE(daily_gain, '%%', '') AS DECIMAL(5,2)) DESC
                                """
                cursor.execute(new_day_query, (date,))
                logging.debug('Query execution for today top gain plates completed')
                all_plate_data_today = cursor.fetchall()
                name_to_rank_today = {plate['name']: idx + 1 for idx, plate in enumerate(all_plate_data_today)}
                
                # 为yesterday_hit_plates添加rank_in_nextday字段
                for item in yesterday_hit_plates:
                    item['rank_in_nextday'] = name_to_rank_today.get(item['name'], None)
                    item['rank_in_yesterday'] = name_to_rank_yesterday.get(item['name'], None)


            
        except Exception:
            logging.debug('Failed to get yesterday hit plates, setting to empty list')
            yesterday_hit_plates = []

        # 今日 TOP20 涨幅板块
        logging.debug('Executing query for today top gain plates')
        query_top_gain = """SELECT name, daily_gain, leading_stock
                          FROM a_share_trading
                          WHERE trade_date = %s
                          ORDER BY CAST(REPLACE(daily_gain, '%%', '') AS DECIMAL(5,2)) DESC
                          LIMIT 20"""
        cursor.execute(query_top_gain, (date,))
        logging.debug('Query execution for today top gain plates completed')
        today_top_gain_plates = cursor.fetchall()

        # 这里需要合并一下数据，将today_top_gain_plates中name字段与yesterday_hit_plates中name字段相同的数据，
        # 合并到一个新的today_top_gain_plates中，合并的规则是：
        # 1. 当yesterday_hit_plates中name字段与today_top_gain_plates中name字段相同的数据，
        #    且yesterday_hit_plates中hit_status字段为'预测已命中'，则增加一个是否命中的字段，设置该字段值为'已命中'，
        for item in today_top_gain_plates:
            for hit_item in yesterday_hit_plates:
                if item['name'] == hit_item['name']:
                    item['forecast_hit_status'] = hit_item['hit_status']
                    break

        cursor.close()
        conn.close()
        logging.debug('Database connection closed for plate data')

        def decimal_default(obj):
            if isinstance(obj, decimal.Decimal):
                return float(obj)
            raise TypeError(f'Object of type {type(obj).__name__} is not JSON serializable')
        
        response = make_response(json.dumps({
            'code': 200,
            'message': 'success',
            'data': {
                'selected_date': date,
                'eliminated_plates_1': eliminated_plates_1,
                'reserved_plates_1': reserved_plates_1,
                'eliminated_plates_2': eliminated_plates_2,
                'reserved_plates_2': reserved_plates_2,
                'all_reserved_plates': all_reserved_plates,
                'top_recommended_plates': top_recommended_plates,
                'yesterday_hit_plates': yesterday_hit_plates,
                'today_top_gain_plates': today_top_gain_plates
            }
        }, sort_keys=False, default=decimal_default))
        response.headers['X-Content-Type-Options'] = 'nosniff'
        response.headers['Cache-Control'] = 'no-store, no-cache, must-revalidate'
        return response
    except pymysql.Error as e:
        logging.error('Database error in get_plate_data: %s', str(e))
        if e.errno == 1048:  # 日期不存在于数据库中
            response = jsonify({
            'code': 404,
            'message': '资源不存在，查询日期不存在于数据库中',
            'error_detail': str(e)
        })
        response.headers['X-Content-Type-Options'] = 'nosniff'
        response.headers['Cache-Control'] = 'no-store, no-cache, must-revalidate'
        return response, 404
    except Exception as e:
        logging.error('General error in get_plate_data: %s', str(e))
        response = jsonify({
            'code': 500,
            'message': '服务器内部错误',
            'error_detail': str(e)
        })
        response.headers['X-Content-Type-Options'] = 'nosniff'
        response.headers['Cache-Control'] = 'no-store, no-cache, must-revalidate'
        return response, 500

import logging

logging.basicConfig(level=logging.DEBUG)

@app.route('/')
def index():
    logging.debug('Accessed root route')
    return send_from_directory('.', 'new_index.html')

@app.route('/<path:path>')
def static_file(path):
    logging.debug(f'Accessed static file route with path: {path}')
    return send_from_directory('.', path)

@app.route(f'{BASE_PATH}/stock-data', methods=['GET'])
def stock_data():
    dates, datasets = get_stock_data()
    return jsonify({
        'dates': dates,
        'datasets': datasets
    })

def get_hedge_plates_info(cursor, date, all_reserved_plates):
    """获取对冲板块信息并更新保留板块数据"""
    logging.debug('Executing query for eliminated plates P')
    query_elim_p = """SELECT name FROM a_share_trading
                      WHERE trade_date = %s AND is_low_performance_eliminate = '是'"""
    cursor.execute(query_elim_p, (date,))
    logging.debug('Query execution for eliminated plates P completed')
    eliminated_plates_p = cursor.fetchall()

    if eliminated_plates_p:
        eliminated_plate_names = [plate['name'] for plate in eliminated_plates_p]
        logging.debug('Executing query for hedge plates H')
        query_hedge_h = """SELECT plate,hedge_plate FROM industry_hedge_relations
                          WHERE plate IN %s"""
        cursor.execute(query_hedge_h, (tuple(eliminated_plate_names),))
        logging.debug('Query execution for hedge plates H completed')
        hedge_plates_h = cursor.fetchall()

        for plate in all_reserved_plates:
            for hedge_plate in hedge_plates_h:
                if plate['name'] == hedge_plate['hedge_plate']:
                    plate['hedge_plate'] = hedge_plate['plate']

    return all_reserved_plates

def get_stock_data():
    try:
        connection = pymysql.connect(**DB_CONFIG)
        with connection.cursor() as cursor:
            # 查询所有板块名称
            cursor.execute("SELECT DISTINCT name FROM a_share_trading")
            plates = cursor.fetchall()

            datasets = []
            for plate in plates:
                plate_name = plate['name']
                # 查询该板块的日期和涨幅数据
                query = """SELECT trade_date as date, daily_gain FROM a_share_trading 
                           WHERE name = %s ORDER BY trade_date"""
                cursor.execute(query, (plate_name,))
                data = cursor.fetchall()

                dates = []
                gains = []
                for item in data:
                    dates.append(str(item['date']))
                    # 去除百分号并转为浮点数
                    gains.append(float(item['daily_gain'].replace('%', '')))

                datasets.append({
                    'label': plate_name,
                    'data': gains,
                    'borderColor': get_random_color(),
                    'fill': False
                })

            # 获取所有日期（假设所有板块日期相同）
            if data:
                all_dates = [str(item['date']) for item in data]
            else:
                all_dates = []

            return all_dates, datasets

    except Exception as e:
        logging.error(f"获取股票数据时出错: {e}")
        return [], []
    finally:
        if 'connection' in locals():
            connection.close()


def get_random_color():
    r = random.randint(0, 255)
    g = random.randint(0, 255)
    b = random.randint(0, 255)
    return f'rgb({r}, {g}, {b})'




if __name__ == '__main__':
    logging.debug('Starting Flask application')
    app.run(debug=True, port=5000)